package dao;

import models.ArticleInfo;
import models.vo.ArticleInfoVO;
import utils.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created with IDEA
 * author:ju bai
 * Date:2021/4/7
 * Time:20:58
 **/
public class ArticleInfoDao {
    /**
     * 使用uid查询文章
     * @param uid
     * @return
     * @throws SQLException
     */
    public List<ArticleInfo> getListByUID(int uid) throws SQLException {
        List<ArticleInfo> list = new ArrayList<>();
        //链接数据库
        Connection connection = DBUtils.getConnection();
        String sql = "select * from articleinfo where uid=?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1,uid);
        //查询数据并返回结果
        ResultSet resultSet = statement.executeQuery();
        while(resultSet.next()){
            ArticleInfo articleInfo = new ArticleInfo();
            articleInfo.setId(resultSet.getInt("id"));
            articleInfo.setTitle(resultSet.getString("title"));
            articleInfo.setContent(resultSet.getString("Content"));
            articleInfo.setCreatetime(resultSet.getDate("createtime"));
            articleInfo.setUpdatetime(resultSet.getDate("updatetime"));
            articleInfo.setRcount(resultSet.getInt("rcount"));
            list.add(articleInfo);
        }
        DBUtils.close(connection,statement,resultSet);
        return list;
    }

    /**
     * 文章的删除
     * @param id
     * @return
     * @throws SQLException
     */
    public int del(int id) throws SQLException {
        int result = 0;
        if(id>0) {
            Connection connection = DBUtils.getConnection();
            String sql = "delete from articleinfo where id=?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1, id);
            result = statement.executeUpdate();
            DBUtils.close(connection, statement, null);
        }
        return result;
    }

    /**
     * 根据id获取文章详情
     * @param id
     * @return
     */
    public ArticleInfoVO getArtById(int id) throws SQLException {
        ArticleInfoVO articleInfoVO = new ArticleInfoVO();
        if(id>0) {
            Connection connection = DBUtils.getConnection();
            String sql = "select a.*,u.username from articleinfo a left join userinfo u on a.uid=u.id where a.id=?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1,id);
            ResultSet resultSet = statement.executeQuery();
            if(resultSet.next()){
                articleInfoVO.setId(resultSet.getInt("id"));
                articleInfoVO.setTitle(resultSet.getString("title"));
                articleInfoVO.setContent(resultSet.getString("content"));
                articleInfoVO.setUsername(resultSet.getString("username"));
                articleInfoVO.setCreatetime(resultSet.getDate("createtime"));
                articleInfoVO.setUpdatetime(resultSet.getDate("updatetime"));
                articleInfoVO.setRcount(resultSet.getInt("rcount"));
            }
            DBUtils.close(connection,statement,resultSet);
        }
        return articleInfoVO;
    }

    /**
     * 编辑文章详情
     * @param id
     * @param title
     * @param content
     * @return
     */
    public int upArt(int id, String title, String content) throws SQLException {
        int result = 0;
        Connection connection = DBUtils.getConnection();
        String sql = "update articleinfo set title=?,content=? where id=?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1,title);
        statement.setString(2,content);
        statement.setInt(3,id);
        result = statement.executeUpdate();
        DBUtils.close(connection,statement,null);
        return result;
    }

    /**
     * 添加文章
     * @param title
     * @param content
     * @param uid
     * @return
     * @throws SQLException
     */
    public int addart(String title, String content, int uid) throws SQLException {
        int result = 0;
        Connection connection = DBUtils.getConnection();
        String sql = "insert into articleinfo(title,content,uid) values(?,?,?)";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1,title);
        statement.setString(2,content);
        statement.setInt(3,uid);
        result = statement.executeUpdate();
        DBUtils.close(connection,statement,null);
        return result;
    }

    /**
     * 分页查看文章详情
     * @param page
     * @param size
     * @return
     * @throws SQLException
     */
    public List<ArticleInfoVO> getListByPage(int page, int size) throws SQLException {
        List<ArticleInfoVO> list = new ArrayList<>();
        Connection connection = DBUtils.getConnection();
        String sql = "select a.*,u.username from articleinfo a left join userinfo u on a.uid=u.id limit ?,?";
        PreparedStatement statement = connection.prepareStatement(sql);
        // 公式：(n-1)*pagesize
        statement.setInt(1, (page - 1) * size);
        statement.setInt(2, size);
        // 执行数据库查询
        ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()) {
            ArticleInfoVO articleInfoVO = new ArticleInfoVO();
            articleInfoVO.setId(resultSet.getInt("id"));
            articleInfoVO.setTitle(resultSet.getString("title"));
            articleInfoVO.setUsername(resultSet.getString("username"));
            articleInfoVO.setCreatetime(resultSet.getDate("createtime"));
            articleInfoVO.setUpdatetime(resultSet.getDate("updatetime"));
            articleInfoVO.setRcount(resultSet.getInt("rcount"));
            list.add(articleInfoVO);
        }
        DBUtils.close(connection, statement, resultSet);
        return list;
    }

    /**
     * 增加阅读量
     * @param id
     * @return
     * @throws SQLException
     */
    public int addrcount(int id) throws SQLException {
        int result = 0;
        Connection connection = DBUtils.getConnection();
        String sql = "update articleinfo set rcount = rcount+1 where id=?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1,id);
        result = statement.executeUpdate();
        DBUtils.close(connection,statement,null);
        return  result;
    }
}
